package com.red.wood.parser;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.junit.Ignore;
import org.junit.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;

import com.red.wood.base.BaseTest;
import com.red.wood.common.ip.IPAddressUtils;
import com.red.wood.common.ip.ReadIPFile;
import com.red.wood.common.ip.ipipnet.Reader;
import com.red.wood.common.springjdbc.CommonDao;
import com.red.wood.common.util.CommonUtils;
import com.red.wood.common.util.ProvinceParse;

@Ignore
public class CdnProvinceParse extends BaseTest{

	private static final Log log = LogFactory.getLog(CdnProvinceParse.class);
	
	@Autowired
	private CommonDao dao;
	
	@Autowired
	@Qualifier("jdbcTemplate")
	private JdbcTemplate jdbcTemplate;
	
	@Test
	@Ignore
	public void cdnDataParse() {
		StringBuilder sqlSb = new StringBuilder("select uuid,cdnid,cdnname,cdnadd,corp,commandid,provinceid,timstamp,filecreatetime from t_dwd_cdn_cdndata where provinceid is null");
		List<Map<String, Object>> list = dao.queryTableData(null, sqlSb);
		if(list != null && list.size() > 0) {
			for(Map<String,Object> map : list) {
				String cdnAdd = CommonUtils.valueOf(map.get("CDNADD"));
				String province = ProvinceParse.addressAdscription(cdnAdd);
				map.put("PROVINCEID",getProvinceId(province));
			}
		}
		dao.deleteTableData("delete from t_dwd_cdn_cdndata where provinceid is null");
		String sql = "insert into t_dwd_cdn_cdndata(uuid,cdnid,cdnname,cdnadd,corp,commandid,provinceid,timstamp,filecreatetime) values (?,?,?,?,?,?,?,?,?)";
		String columns = "uuid,cdnid,cdnname,cdnadd,corp,commandid,provinceid,timstamp,filecreatetime";
		dao.insertBatchTableData(list, sql, columns,5000);
	}
	
	@Test
	@Ignore
	public void cdnCustomerParse() {
		IPAddressUtils ip = new IPAddressUtils();
		ip.init();
		
		StringBuilder sqlSb = new StringBuilder("select uuid,cdnid,customeruuid,customerid,unitname,unitnature,idtype,idnumber,address,provinceid,timstamp,filecreatetime from t_dwd_cdn_customerinfo where provinceid is null and address is not null");
		List<Map<String, Object>> list = dao.queryTableData(null, sqlSb);
		
		for(Map<String,Object> map : list) {
			String address = CommonUtils.valueOf(map.get("ADDRESS"));
			
			String province = ProvinceParse.addressAdscription(address);
			map.put("PROVINCEID",getProvinceId(province));
		}
		
		dao.deleteTableData("delete from t_dwd_cdn_customerinfo where provinceid is null and address is not null");
		String sql = "insert into t_dwd_cdn_customerinfo(uuid,cdnid,customeruuid,customerid,unitname,unitnature,idtype,idnumber,address,provinceid,timstamp,filecreatetime) values (?,?,?,?,?,?,?,?,?,?,?,?)";
		String columns = "uuid,cdnid,customeruuid,customerid,unitname,unitnature,idtype,idnumber,address,provinceid,timstamp,filecreatetime";
		dao.insertBatchTableData(list, sql, columns,10000);
	}
	
	@Test
	@Ignore
	public void cdnIpParse() throws Exception {
		StringBuilder sqlSb = new StringBuilder("select cdnId,cdnNetId,nodeId,houseId,id,startIp,endIp,type,timstamp,fileCreateTime,reportProvinceCity,reportTime from t_cdn_cdnnet_node_house_ipseg where 1 = 1");
		List<Map<String, Object>> list = dao.queryTableData(null, sqlSb);
		
		for(Map<String,Object> map : list) {
			String startIp = CommonUtils.valueOf(map.get("startIp"));
		
			String[] area = ReadIPFile.getInstance().find(startIp);
			
			map.put("province", area[1]);
		}
		
		dao.deleteTableData("delete from t_cdn_cdnnet_node_house_ipseg");
		String sql = "insert into t_cdn_cdnnet_node_house_ipseg(province,cdnId,cdnNetId,nodeId,houseId,id,startIp,endIp,type,timstamp,fileCreateTime,reportProvinceCity,reportTime) values (?,?,?,?,?,?,?,?,?,?,?,?,?)";
		String columns = "province,cdnId,cdnNetId,nodeId,houseId,id,startIp,endIp,type,timstamp,fileCreateTime,reportProvinceCity,reportTime";
		dao.insertBatchTableData(list, sql, columns,10000);
	}
	
	@Test
	@Ignore
	public void cdnDomainParse() throws Exception {
		/*StringBuilder sqlSb = new StringBuilder("select cdnId,cdnNetId,nodeId,houseId,id,startIp,endIp,type,timstamp,fileCreateTime,reportProvinceCity,reportTime from t_cdn_cdnnet_node_house_ipseg where 1 = 1");
		List<Map<String, Object>> list = dao.queryTableData(null, sqlSb);
		
		for(Map<String,Object> map : list) {
			String startIp = CommonUtils.valueOf(map.get("startIp"));
		
			String[] area = ReadIPFile.getInstance().find(startIp);
			
			map.put("province", area[1]);
		}
		
		dao.deleteTableData("delete from t_cdn_cdnnet_node_house_ipseg");
		String sql = "insert into t_cdn_cdnnet_node_house_ipseg(province,cdnId,cdnNetId,nodeId,houseId,id,startIp,endIp,type,timstamp,fileCreateTime,reportProvinceCity,reportTime) values (?,?,?,?,?,?,?,?,?,?,?,?,?)";
		String columns = "province,cdnId,cdnNetId,nodeId,houseId,id,startIp,endIp,type,timstamp,fileCreateTime,reportProvinceCity,reportTime";
		dao.insertBatchTableData(list, sql, columns,10000);*/
		String key = "a";
		System.err.println(getProvinceMap(key));
	}
	
	@Test
	@Ignore
	public void ircsDataParse() {
		StringBuilder sqlSb = new StringBuilder("select uuid,ircsid,ircsname,ircsadd,ircszip,corp,housecount,usercount,commandid,provinceid,timstamp,filecreatetime from t_dwd_ircs_ircsdata where provinceid is null");
		List<Map<String, Object>> list = dao.queryTableData(null, sqlSb);
		
		for(Map<String,Object> map : list) {
			String ircsAdd = CommonUtils.valueOf(map.get("IRCSADD"));
		
			String province = ProvinceParse.addressAdscription(ircsAdd);
			map.put("PROVINCEID", getProvinceId(province));
		}
		
		dao.deleteTableData("delete from t_dwd_ircs_ircsdata where provinceid is null");
		String sql = "insert into t_dwd_ircs_ircsdata(uuid,ircsid,ircsname,ircsadd,ircszip,corp,housecount,usercount,commandid,provinceid,timstamp,filecreatetime) values (?,?,?,?,?,?,?,?,?,?,?,?)";
		String columns = "uuid,ircsid,ircsname,ircsadd,ircszip,corp,housecount,usercount,commandid,provinceid,timstamp,filecreatetime";
		dao.insertBatchTableData(list, sql, columns,5000);
	}
	
	@Test
	@Ignore
	public void ircsIpParse() throws Exception {
		
		StringBuilder sqlSb = new StringBuilder("select uuid,ircsid,ipinfouuid,ipinfoid,startip,endip,type,sourceunit,allocationunit,allocationtime,provinceid,timstamp,filecreatetime from t_dwd_ircs_ipinfo where provinceid is null"); 
		List<Map<String, Object>> list = dao.queryTableData(null, sqlSb);
		String path = CdnProvinceParse.class.getResource("/ipiptest.ipdb").getPath();
		Reader db = new Reader("/ipiptest.ipdb");
		for(Map<String,Object> map : list) {
			String startIp = CommonUtils.valueOf(map.get("STARTIP"));
			map.put("PROVINCEID", getProvinceId(db.find(startIp, "CN")[1]));
		}
		
		dao.deleteTableData("delete from t_dwd_ircs_ipinfo where provinceid is null");
		String sql = "insert into t_dwd_ircs_ipinfo(uuid,ircsid,ipinfouuid,ipinfoid,startip,endip,type,sourceunit,allocationunit,allocationtime,provinceid,timstamp,filecreatetime) values (?,?,?,?,?,?,?,?,?,?,?,?,?)";
		String columns = "uuid,ircsid,ipinfouuid,ipinfoid,startip,endip,type,sourceunit,allocationunit,allocationtime,provinceid,timstamp,filecreatetime";
		dao.insertBatchTableData(list, sql, columns,5000);
	}
	
	@Test
	@Ignore
	public void ircsUser() {
		//List<Map<String,Object>> list = jdbcTemplate.queryForList("select t.ircsid,t.userid,t.unitname,t.idtype,t.idno,t.unitnature,t.officermobile,t.officertel,t.officeremail,t.address,t.zipcode,t.registertime from t_ods_ircs_queryUserInfo t");
		/*StringBuilder sqlSb = new StringBuilder("select ircsId,userId,unitName,nature,idType,unitName,officerName,officerMobile,officerTel,unitadd,zipCode,registerTime,timstamp from t_ircs_userinfo_result");
		List<Map<String, Object>> list = dao.queryTableData(null, sqlSb);
		
		for(Map<String,Object> map : list) {
			String cdnAdd = CommonUtils.valueOf(map.get("unitadd"));
		
			String province = C3Parse.addressAdscription(cdnAdd);
			map.put("province", province);
		}
		
		dao.deleteTableData("delete from t_ircs_userinfo_result");
		String sql = "insert into t_ircs_userinfo_result(province,ircsId,userId,unitName,nature,idType,unitName,officerName,officerMobile,officerTel,unitadd,zipCode,registerTime,timstamp) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		String columns = "province,ircsId,userId,unitName,nature,idType,unitName,officerName,officerMobile,officerTel,unitadd,zipCode,registerTime,timstamp";
		dao.insertBatchTableData(list, sql, columns,10000);*/

		try {
			int offset = 0;
			int pageSize = 10000;
			int totalCount = 0;
			int i = 0;
			do{
				//由于数据量比较大，一次性无法将所有数据传过来，故做分页处理，每次处理10000条数据
				List<Map<String,Object>> list = getIrcsUserInfo(pageSize, offset);
				for(Map<String,Object> map : list) {
					String cdnAdd = CommonUtils.valueOf(map.get("unitadd"));
				
					String province = ProvinceParse.addressAdscription(cdnAdd);
					map.put("province", province);
				}
				String sql = "insert into t_ircs_userinfo_result(province,ircsId,userId,nature,idType,unitName,unitNature,officerName,officerMobile,officerTel,unitadd,zipCode,registerTime,timstamp) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
				String columns = "province,ircsId,userId,nature,idType,unitName,unitNature,officerName,officerMobile,officerTel,unitadd,zipCode,registerTime,timstamp";
				if(i == 0 && list != null && list.size() > 0) {
					dao.deleteTableData("delete from t_ircs_userinfo_result");
				}
				dao.insertBatchTableData(list, sql, columns,10000);
				i++;
				totalCount = list.size();
				offset = offset + pageSize;
			}while(pageSize == totalCount);
		}catch(Exception e) {
			e.printStackTrace();
		}
	
	}
	@Test
	@Ignore
	public void ircsIpGetWayParse() throws Exception {
		
		StringBuilder sqlSb = new StringBuilder("select ircsid,gatewayuuid,gatewayid,bandwidth,linktype,accessunit,gatewayip,internetaccess,provinceid,timstamp,filecreatetime from t_dwd_ircs_gatewayinfo where provinceid is null");
		List<Map<String, Object>> list = dao.queryTableData(null, sqlSb);
		Reader db = new Reader("/ipiptest.ipdb");
		
		for(Map<String,Object> map : list) {
			String gatewayIp = CommonUtils.valueOf(map.get("GATEWAYIP"));
			map.put("PROVINCEID", getProvinceId(db.find(gatewayIp, "CN")[1]));
		}
		
		dao.deleteTableData("delete from t_dwd_ircs_gatewayinfo where provinceid is null");
		String sql = "insert into t_dwd_ircs_gatewayinfo(ircsid,gatewayuuid,gatewayid,bandwidth,linktype,accessunit,gatewayip,internetaccess,provinceid,timstamp,filecreatetime) values (?,?,?,?,?,?,?,?,?,?,?)";
		String columns = "ircsid,gatewayuuid,gatewayid,bandwidth,linktype,accessunit,gatewayip,internetaccess,provinceid,timstamp,filecreatetime";
		dao.insertBatchTableData(list, sql, columns,10000);
	}
	
	
	public List<Map<String, Object>> getIrcsUserInfo(int pageSize, int offset) {
		StringBuffer sql = new StringBuffer();
		sql.append("select ircsId,userId,unitName,nature,idType,unitNature,officerName,officerMobile,officerTel,unitadd,zipCode,registerTime,timstamp from t_ircs_userinfo_result1");
		sql.append(" limit "+pageSize +" offset "+offset);
		return jdbcTemplate.queryForList(sql.toString());
	}
	
	public String getProvinceId(String name) {
		Map<String,String> map = new HashMap<String,String>();
		map.put("台湾","710000");
		map.put("河南","410000");
		map.put("江西","360000");
		map.put("吉林","220000");
		map.put("陕西","610000");
		map.put("山西","140000");
		map.put("上海","310000");
		map.put("江苏","320000");
		map.put("浙江","330000");
		map.put("黑龙江","230000");
		map.put("四川","510000");
		map.put("山东","370000");
		map.put("湖北","420000");
		map.put("内蒙古","150000");
		map.put("辽宁","210000");
		map.put("湖南","430000");
		map.put("海南","460000");
		map.put("重庆","500000");
		map.put("广西","450000");
		map.put("福建","350000");
		map.put("贵州","520000");
		map.put("云南","530000");
		map.put("青海","630000");
		map.put("香港","810000");
		map.put("澳门","820000");
		map.put("安徽","340000");
		map.put("北京","110000");
		map.put("天津","120000");
		map.put("河北","130000");
		map.put("广东","440000");
		map.put("西藏","540000");
		map.put("甘肃","620000");
		map.put("宁夏","640000");
		map.put("新疆","650000");
		String provinceId = map.get(name);
		return StringUtils.isNotBlank(provinceId) ? provinceId : "";
	}
	
	public String getProvinceMap(String key){
		Map<String,String> map = new HashMap<String,String>();
		map.put("京","北京");	
		map.put("津","天津");	
		map.put("冀","河北");	
		map.put("晋","山西");	
		map.put("蒙","内蒙古");
		map.put("辽","辽宁");	
		map.put("吉","吉林");	
		map.put("黑","黑龙江");
		map.put("沪","上海");	
		map.put("苏","江苏");	
		map.put("浙","浙江");	
		map.put("皖","安徽");	
		map.put("闽","福建");	
		map.put("赣","江西");	
		map.put("鲁","山东");	
		map.put("豫","河南");	
		map.put("鄂","湖北");	
		map.put("湘","湖南");	
		map.put("粤","广东");	
		map.put("桂","广西");	
		map.put("琼","海南");	
		map.put("渝","重庆");	
		map.put("川","四川");	
		map.put("黔","贵州");	
		map.put("滇","云南");	
		map.put("藏","西藏");	
		map.put("陕","陕西");	
		map.put("甘","甘肃");	
		map.put("青","青海");	
		map.put("宁","宁夏");	
		map.put("新","新疆");	
		map.put("台","台湾");	
		map.put("港","香港");	
		map.put("澳","澳门");	
		String province = "";
		if(StringUtils.isNotBlank(key) || key.length() > 1) {
			province = map.get(key.substring(0, 1));
		}
		return StringUtils.isNotBlank(province) ? province : "";
	}
}
